﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Demo01.Repsotories;
using MD5Hash;
using WindowsFormsApp1.Models;
using WindowsFormsApp1.Utils;


namespace WindowsFormsApp1.Respostories
{
    public  class UserRespostoriy
    {
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="mobile"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static User Login(string mobile, string password)
        {
            User user = null;
            SqlDataReader reader = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Mobile", mobile),
                new SqlParameter("@Password", password)
            };
            //reader = DBHelper.Query("SELECT Id, Mobile, Password, State, Name, RoleId FROM Users WHERE Mobile = @Mobile AND Password = @Password", parameters);
            string sql = "SELECT Id, Mobile, Password, State, Name, RoleId FROM Users WHERE Mobile = @Mobile AND Password = @Password";
            try
            {
                reader = DBHelper.Query(sql, parameters);
                while (reader.Read())
                {
                    user = new User();
                    user.Id = reader.GetString(0);//Id
                    user.Mobile = reader.GetString(1);//手机号
                    user.Password = reader.GetString(2);//密码
                    user.State = reader.GetInt32(3);//状态
                    user.Name = reader.GetString(4);//姓名
                    user.RoleId = reader.GetInt32(5);//角色Id
                    user.RoleName = GlobalData.RoomsInfo[user.RoleId.ToString()];//角色名称
                    
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(" 用户信息读取数据库失败：" + ex.Message);
            }

            finally
            {
                reader?.Close();
            }
            return user;
        }




        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        public static List<User> GetUsers()
        {
            List<User> users = new List<User>();
            SqlDataReader reader=null;
            string sql = "SELECT Users.Id,Users.Mobile,Users.Password,Users.State,Users.Name,Users.RoleId, Roles.Name FROM Users, Roles WHERE Users.RoleId = Roles.Id ";

            try
            {

                reader = DBHelper.Query(sql);
                while (reader.Read())
                {
                    User user = new User();
                    user.Id = reader.GetString(0);//Id
                    user.Mobile = reader.GetString(1);//手机号
                    user.Password = reader.GetString(2);//密码
                    user.State = reader.GetInt32(3);//状态
                    user.Name = reader.GetString(4);//姓名
                    user.RoleId = reader.GetInt32(5);//角色Id
                    user.RoleName = reader.GetString(6);//角色名称
                    users.Add(user);
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(" 用户信息读取数据库失败："+ex.Message);
            }
            finally
            {
                reader.Close();
            }
            return users;
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns> 是否成功 ,true:成功，false:失败</returns>
        public static bool AddUser(User user)
        {
            bool isSuccess = false;
            try
            {
                string sql = "insert into Users(Id, Mobile,Password,State,Name,RoleId) values(@Id,@Mobile,@Password,@State,@Name,@RoleId)";
                SqlParameter[] parameters = {
                    new SqlParameter("@Id",user.Id),
                    new SqlParameter("@Mobile",user.Mobile),
                    //使用MD5加密
                    new SqlParameter("@Password",user.Password.GetMD5()),
                    new SqlParameter("@State",user.State),
                    new SqlParameter("@Name",user.Name),
                    new SqlParameter("@RoleId",user.RoleId)
                };
                int count = DBHelper.ExecuteNonQuery(sql, parameters);
                if (count > 0)
                {
                    isSuccess = true;
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("新增用户失败：" + ex.Message);
            }
            return isSuccess;
        }

        /// <summary>
        /// 重置密码操作
        /// </summary>
        /// <param name="user"></param>
        /// <returns>是否成功 ,true:成功，false:失败 </returns>
        public static bool ResetPassword(User user)
        {
            bool isSuccess = false;
            try
            {
                string sql = "update Users set Password=@Password where Id=@Id";
                SqlParameter[] parameters = {
                    //重置后的密码是手机号后四位
                    new SqlParameter("@Password",user.Mobile.Substring(user.Mobile.Length-4).GetMD5()),
                    new SqlParameter("@Id",user.Id)
                };
                int count = DBHelper.ExecuteNonQuery(sql, parameters);
                if (count > 0)
                {
                    isSuccess = true;
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("未找到数据库用户信息：" + ex.Message);
            }
            return isSuccess;
        }

        /// <summary>
        /// 修改密码操作
        /// </summary>
        /// <param name="user"></param>
        /// <returns>是否成功 ,true:成功，false:失败 </returns>
        public static bool updatePassword(User user,string NewPassword)
        {
            bool isSuccess = false;
            try
            {
                string sql = "update Users set Password=@Password where Id=@Id";
                SqlParameter[] parameters = {
                    //重置后的密码是手机号后四位
                    new SqlParameter("@Password",NewPassword.GetMD5()),
                    new SqlParameter("@Id",user.Id)
                };
                int count = DBHelper.ExecuteNonQuery(sql, parameters);
                if (count > 0)
                {
                    isSuccess = true;
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("未找到数据库用户信息" + ex.Message);
            }
            return isSuccess;
        }


        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns> 是否成功 ,true:成功，false:失败</returns>
        public static bool DeleteUser(User user)
        {
            bool isSuccess = false;
            try
            {
                string sql = "delete from Users where Id=@Id";
                SqlParameter[] parameters = {
                    new SqlParameter("@Id",user.Id)
                };
                int count = DBHelper.ExecuteNonQuery(sql, parameters);
                if (count > 0)
                {
                    isSuccess = true;
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("删除用户失败：" + ex.Message);
            }
            return isSuccess;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static bool UpdateUser(User user)
        {
            bool isSuccess = false;
            try
            {
                string sql = "update Users set Mobile=@Mobile,Name=@Name,RoleId=@RoleId ,State=@State where Id=@Id";
                SqlParameter[] parameters = {
                    new SqlParameter("@Mobile",user.Mobile),
                    new SqlParameter("@Name",user.Name),
                    new SqlParameter("@RoleId",user.RoleId),
                    new SqlParameter("@Id",user.Id),
                    new SqlParameter("@State",user.State)
                };
                int count = DBHelper.ExecuteNonQuery(sql, parameters);
                if (count > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    MessageBox.Show("未找到用户信息");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("更新用户信息数据库失败：" + ex.Message);
            }
            return isSuccess;
        }

        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <returns></returns>
        public static List<User> QueryUsers(string roleName,string keyWord,int state)
        {
            List<User> users = new List<User>();
            SqlDataReader reader = null;
            // 角色名称模糊查询
            string sql = "SELECT Users.Id,Users.Mobile,Users.Password,Users.State,Users.Name,Users.RoleId, Roles.Name FROM Users, Roles WHERE Users.RoleId = Roles.Id";
            if(roleName != "")
                sql += " AND Roles.Name LIKE '%"+roleName+"%'";
            // 判断关键字是姓名还是手机号
            if(keyWord.Length == 11)
            {
                sql += " AND Users.Mobile LIKE '%" + keyWord + "%'";
            }
            else
            {
                sql += " AND Users.Name LIKE '%" + keyWord + "%'";
            }
                
            // 状态查询
            if(state != -1)
                sql += " AND Users.State = "+state;
            
            try
            {
                reader = DBHelper.Query(sql);

                while (reader.Read())
                {
                    User user = new User();
                    user.Id = reader.GetString(0);//Id
                    user.Mobile = reader.GetString(1);//手机号
                    user.Password = reader.GetString(2);//密码
                    user.State = reader.GetInt32(3);//状态
                    user.Name = reader.GetString(4);//姓名
                    user.RoleId = reader.GetInt32(5);//角色Id
                    user.RoleName = GlobalData.RoomsInfo[user.RoleId.ToString()];//角色名称
                    users.Add(user);
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(" 用户信息读取数据库失败：" + ex.Message);
            }
            finally
            {
                reader.Close();
            }
            return users;
        }
    }
}
